
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_DimCommodity_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_DimCommodity_Insert]
GO


create procedure dbo.p_DimCommodity_Insert (
	@CommodityIdentifier varchar (20),
	@CommodityName nvarchar (50),
	@BrandName nvarchar (100),
	@SupplierName nvarchar (100),
	@ValueRange varchar (50),
	@Value numeric (18, 3),
	@IsTangible varchar (3),
	@CommodityLine int,
	@ReplacementRate varchar (50),
	@GrossMargin varchar (20),
	@DurationOfSatisfaction varchar (20),
	@DurationOfSearch varchar (20),
	@Model varchar (30),
	@ModelVariant varchar (30),
	@ModelNumber varchar (30),
	@ManufacturerPartNumber varchar (30),
	@SerialNumber varchar (40),
	@BarcodeNumber varchar (40),
	@SizeRange varchar (30),
	@Color varchar (30),
	@CommodityKey int output)
as
begin

	-- Disable row counting
	set nocount on

	declare @Error int,
	        @inTranCount int,
	        @Rows int

	select @Error = 0,
	       @inTranCount = @@trancount

	-- Only start our own transaction if caller has not supplied one
	if (@inTranCount = 0) begin transaction txnDimCommodityInsert
        else
	    save transaction txnDimCommodityInsert

	set @Error = @@error
	if (@Error <> 0) goto cleanup

	insert into dbo.DimCommodity (CommodityIdentifier,
				      CommodityName,
				      BrandName,
				      SupplierName,
				      ValueRange,
				      Value,
				      IsTangible,
				      CommodityLine,
				      ReplacementRate,
				      GrossMargin,
				      DurationOfSatisfaction,
				      DurationOfSearch,
				      Model,
				      ModelVariant,
				      ModelNumber,
				      ManufacturerPartNumber,
				      SerialNumber,
				      BarcodeNumber,
				      SizeRange,
				      Color)
	  values (@CommodityIdentifier,
		  @CommodityName,
		  @BrandName,
		  @SupplierName,
		  @ValueRange,
		  @Value,
		  @IsTangible,
		  @CommodityLine,
		  @ReplacementRate,
		  @GrossMargin,
		  @DurationOfSatisfaction,
		  @DurationOfSearch,
		  @Model,
		  @ModelVariant,
		  @ModelNumber,
		  @ManufacturerPartNumber,
		  @SerialNumber,
		  @BarcodeNumber,
		  @SizeRange,
		  @Color)

	select @Error = @@error,
	       @Rows = @@rowcount
	if ((@Error = 0) and
	    (@Rows = 0)) set @Error = -1
	if (@Error <> 0) goto cleanup

	set @CommodityKey = scope_identity()

cleanup:

	-- End transaction
	if (@@trancount > 0)
	begin

		-- On error roll back to save point or local transaction else commit
		if (@Error <> 0) rollback transaction txnDimCommodityInsert
		else
		if (@inTranCount = 0) commit transaction txnDimCommodityInsert

	end

	return @Error

end
GO

grant execute on dbo.p_DimCommodity_Insert  to [db_datawriter]
go
